#!C:\perl\bin
use strict;
use warnings;
use lib "/perl/lib";
use Spreadsheet::ParseExcel;
use DBI;
use utf8;
use Encode;

# connection a sql
my $dbh=DBI->connect("DBI:Pg:dbname='mmdn';host=localhost","postgres","mmdn",{'RaiseError'=>1});

my $fichier=$ARGV[0];
my $exp=encode_utf8($ARGV[1]);

my $worksheet;
my $parser=Spreadsheet::ParseExcel->new();
my $workbook=$parser->parse("$fichier");
if (!defined $workbook){
    die $parser->error(),".\n";
}

foreach($workbook->worksheets()){
    $worksheet=$_;
}
    
my ($row_min,$row_max) = $worksheet->row_range();
my ($col_min, $col_max) = $worksheet->col_range();
my %result;
my %test=();
my $cpt =0;

for my $row (1 .. $row_max){ #parcours lignes
    my $sexe;
    my $code;
    my $histo;
    my $anti;
    my $date;
    my $lame;
    my $obs;
    my $diag;
    
    for my $col ($col_min .. $col_max){ #parcours colonnes
        if($col eq 0){
            my $cont=$worksheet->get_cell($row,$col);
            if($cont){
                $sexe=$cont->value();
            }
        }
        elsif($col eq 1){
            my $cont=$worksheet->get_cell($row,$col);
            if($cont){
                $code=$cont->value();
            }
        }
        elsif($col eq 2){
            my $cont=$worksheet->get_cell($row,$col);
            if($cont){
                $histo=$cont->value();
            }
        }
        elsif($col eq 3){
            my $cont=$worksheet->get_cell($row,$col);
            if($cont){
                $anti=$cont->value();
            }
        }
        elsif($col eq 4){
            my $cont=$worksheet->get_cell($row,$col);
            if($cont){
                $date=$cont->value();
            }
        }
        elsif($col eq 5){
            my $cont=$worksheet->get_cell($row,$col);
            if($cont){
                $lame=$cont->value();
            }
        }
        elsif($col eq 6){
            my $cont=$worksheet->get_cell($row,$col);
            if($cont){
                $obs=$cont->value();
            }
        }
        elsif($col eq 7){
            my $cont=$worksheet->get_cell($row,$col);
            if($cont){
                $diag=$cont->value();
            }
        }
    }
    if($code){
        if(%test){
            if($test{'code'} eq $code and $test{'histo'}=$histo){
                if($obs){ $test{$lame}=$obs;}
                else{ $test{$lame}='';}
            }
            else{
                $result{$cpt}={%test};
                %test=();
                $cpt++;
                $test{'code'}=$code;
                $test{'histo'}=$histo;
                $test{'sexe'}=$sexe;
                $test{'date'}=$date;
                $test{'anti'}=$anti;
                $test{'diagnostic'}=$diag;
                if($obs){ $test{$lame}=$obs; }
                else{ $test{$lame}='';}
            }
        }
        else{
            $test{'code'}=$code;
            $test{'histo'}=$histo;
            $test{'sexe'}=$sexe;
            $test{'date'}=$date;
            $test{'anti'}=$anti;
            $test{'diagnostic'}=$diag;
            if($obs){ $test{$lame}=$obs; }
            else{ $test{$lame}='';}
        }
    }
    if(not $code and %test){
        $result{$cpt}={%test};
    }
}

#~ foreach my $key(sort keys %result){
    #~ foreach my $key2 (sort keys %{$result{$key}}){
        #~ print "$key2: "; 
        #~ if($result{$key}{$key2}){
            #~ print "$result{$key}{$key2}, ";
        #~ }
    #~ }
    #~ print "\n";
#~ }

# ********************* INSERTION DONNEES DANS LA BASE **************************** 

# ************************************* Création type_test ************************************* #
my $id_type_test;
$id_type_test=$dbh->selectrow_array("SELECT id_type_test FROM type_test WHERE label='Test immunologique'");

if(not $id_type_test){
    $dbh->do("INSERT INTO Type_test(label) VALUES ('Test immunologique')"); 
    $id_type_test=$dbh->selectrow_array("SELECT id_type_test FROM type_test WHERE label='Test immunologique'");
}

foreach my $key(sort keys %result){
    my $sexe=$result{$key}{'sexe'};
    my $code=$result{$key}{'code'};
    my $histo=$result{$key}{'histo'};
    my $date=$result{$key}{'date'};
    my $anti=$result{$key}{'anti'};
    my $diag=$result{$key}{'diagnostic'};
    
    
    # ************************************* Création/mise à jour animal ************************************* #
    my $prep_anim=$dbh->prepare("SELECT id_animal,sexe,code_histologique FROM Animal WHERE code_animal=(E'$code')");
    my $id_anim;
    my $sexe2;
    my $histo2;
    
    $prep_anim->execute();
    while( my $ref=$prep_anim->fetchrow_hashref()){
        $id_anim=$ref->{'id_animal'};
        $sexe2=$ref->{'sexe'};
        $histo2=$ref->{'code_histologique'};
    }
    
    if(not $id_anim){
        $dbh->do("INSERT INTO Animal(code_animal, code_histologique, sexe) VALUES ('$code',$histo,'$sexe')");
        $id_anim=$dbh->selectrow_array("SELECT id_animal FROM animal WHERE code_animal=(E'$code') AND code_histologique=$histo");
    }
    if($id_anim and not $sexe2){
        $dbh->do("UPDATE Animal SET sexe=(E'$sexe') WHERE id_animal=$id_anim");
    }
    if($id_anim and not $histo2){
        $dbh->do("UPDATE Animal SET code_histologique=$histo WHERE id_animal=$id_anim");
    }
    
    # ************************************* Création test ************************************* #
    my $id_test2;
    ### verification que le test n'existe pas deja ###
    $id_test2=$dbh->selectrow_array("SELECT id_test FROM Test WHERE id_type_test=$id_type_test AND date_test=(E'$date') AND experimentateur='$exp'");
            
    ### ajout du test ###
    if(not $id_test2){
        $dbh->do("INSERT INTO Test(date_test,id_type_test,experimentateur) VALUES ('$date',$id_type_test,'$exp')");
        $id_test2=$dbh->selectrow_array("SELECT id_test FROM Test WHERE id_type_test=$id_type_test AND date_test=(E'$date') AND experimentateur='$exp'");
    }
    
    # ************************************* Création resultat_test ************************************* #
    my $id_res2;
    $id_res2=$dbh->selectrow_array("SELECT id_res from resultat_test WHERE id_test=$id_test2 and id_animal=$id_anim");
    
    if(not $id_res2){
        $dbh->do("INSERT INTO Resultat_test(id_test, id_animal) VALUES($id_test2, $id_anim)");
        $id_res2=$dbh->selectrow_array("SELECT id_res from resultat_test WHERE id_test=$id_test2 and id_animal=$id_anim");
    }

    # ************************************* Création test_immunologique ************************************* #
    my $id_res3;
    $id_res3=$dbh->selectrow_array("SELECT id_res FROM test_immunologique WHERE id_res=$id_res2");
    
    if(not $id_res3){
        if($diag){ $dbh->do("INSERT INTO Test_immunologique (code_anticorps,diagnostic,id_res) VALUES ('$anti','$diag',$id_res2)"); }
        else{ $dbh->do("INSERT INTO Test_immunologique (code_anticorps,id_res) VALUES ('$anti',$id_res2)"); }
        $id_res3=$dbh->selectrow_array("SELECT id_res FROM test_immunologique WHERE id_res=$id_res2");
    }
    
    # ************************************* Création lame_immuno ************************************* #
    foreach my $key2(sort keys %{$result{$key}}){
        if($key2=~/^\d{1,4}$/){
            my $id_res4;
            $id_res4=$dbh->selectrow_array("SELECT id_res FROM Lame_immuno WHERE id_res=$id_res3 AND num_lame=$key2");
            
            if(not $id_res4){
                if($result{$key}{$key2} ne ''){
                    my $obs=$result{$key}{$key2};
                    my $temp=$dbh->quote($obs);
                    my $new=encode_utf8($temp);
                    $dbh->do("INSERT INTO Lame_immuno(num_lame,id_res,observation) VALUES ($key2,$id_res3,$new)");
                }
                else{
                    $dbh->do("INSERT INTO Lame_immuno(num_lame,id_res) VALUES ($key2,$id_res3)");
                }
            }
        }
    }
}

$dbh->disconnect();
